﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Security.Cryptography;
using System.IO;

namespace Project
{
    public partial class Main : Form
    {
        private OleDbConnection bookConn;
        private OleDbCommand oleDbCmd = new OleDbCommand();
        private String connParam = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Properties.Settings.Default.database + ";Persist Security Info=False";
        string currentId;
        string selectedloanID;
        string selectedloanID1;
        string bookID;
        string bookName;


        public Main()
        {
            bookConn = new OleDbConnection(connParam);
            InitializeComponent();
        }

        private void Main_Load(object sender, EventArgs e)
        {
            gridStudent.ForeColor = System.Drawing.Color.Black;
            notification(Properties.Settings.Default.notificationDays);
            
        }

        private void getID(string id)
        {
            currentId = id;
            lblUser.Text = id;


        }
        private void prodReturn(string id)
        {
            bookConn.Open();
            oleDbCmd.Connection = bookConn;
            DateTime time = DateTime.Now;
            string dateFormat = "dd/MM/yyyy";



            oleDbCmd.CommandText = "UPDATE loan SET status = 'returned', actualEnd_date = '" + time.ToString(dateFormat) + "' WHERE ID = " + id + ";";

            try
            {
                int temp = oleDbCmd.ExecuteNonQuery();
                if (temp > 0)
                {

                    MessageBox.Show("Item Returned Successfully.");

                }
                else
                {
                    MessageBox.Show("Record Fail to Added");
                }
            }
            catch
            {
                MessageBox.Show("Record Fail to Added");
            }
            bookConn.Close();

        }

        private void GetStudentDetails(string ID)
        {

            getID(ID);
            sqlCommands result = new sqlCommands();
            string sqlStatement = result.studentDetail(ID);
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(sqlStatement, connParam);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();
            dAdapter.Fill(dataTable);


            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                txtFname.Text = dataTable.Rows[i][1].ToString();
                txtLname.Text = dataTable.Rows[i][2].ToString();
                txtID.Text = dataTable.Rows[i][3].ToString();

                txtEmail.Text = dataTable.Rows[i][6].ToString();

            }
            loanview.DataSource = null;
            loanview.Rows.Clear();
            loanview.Refresh();

            string sqlStatement1 = result.studentLoan(ID);
            OleDbDataAdapter dAdapter1 = new OleDbDataAdapter(sqlStatement1, connParam);
            OleDbCommandBuilder cBuilder1 = new OleDbCommandBuilder(dAdapter1);
            DataTable dataTable1 = new DataTable();
            DataSet ds1 = new DataSet();
            dAdapter1.Fill(dataTable1);




            string rentStart;
            string rentEnd;



            for (int i = 0; i < dataTable1.Rows.Count; i++)
            {

                rentStart = dataTable1.Rows[i][3].ToString();
                rentEnd = dataTable1.Rows[i][4].ToString();
                DateTime startDate;
                DateTime endDate;
                startDate = new DateTime();
                endDate = new DateTime();
                startDate = DateTime.ParseExact(rentStart, "dd/MM/yyyy", null);
                endDate = DateTime.ParseExact(rentEnd, "dd/MM/yyyy", null);


                int diffDays = (endDate.Date - startDate.Date).Days;
                loanview.Rows.Add(dataTable1.Rows[i][0], dataTable1.Rows[i][6], dataTable1.Rows[i][3], dataTable1.Rows[i][4], diffDays);
            }


            studentLoanReturn.DataSource = null;
            studentLoanReturn.Rows.Clear();
            studentLoanReturn.Refresh();
            string sqlStatement2 = result.studentLoanReturn(ID);
            OleDbDataAdapter dAdapter2 = new OleDbDataAdapter(sqlStatement2, connParam);
            OleDbCommandBuilder cBuilder2 = new OleDbCommandBuilder(dAdapter2);
            DataTable dataTable2 = new DataTable();
            DataSet ds2 = new DataSet();
            dAdapter2.Fill(dataTable2);

            for (int i = 0; i < dataTable2.Rows.Count; i++)
            {

                studentLoanReturn.Rows.Add(dataTable2.Rows[i][0], dataTable2.Rows[i][6], dataTable2.Rows[i][3], dataTable2.Rows[i][7]);
            }


            studentHold.DataSource = null;
            studentHold.Rows.Clear();
            studentHold.Refresh();
            string sqlStatement3 = result.studentHold(ID);
            OleDbDataAdapter dAdapter3 = new OleDbDataAdapter(sqlStatement3, connParam);
            OleDbCommandBuilder cBuilder3 = new OleDbCommandBuilder(dAdapter3);
            DataTable dataTable3 = new DataTable();
            DataSet ds3 = new DataSet();
            dAdapter3.Fill(dataTable3);

            for (int i = 0; i < dataTable3.Rows.Count; i++)
            {
                string avaialable = "No";
                string tempVar = dataTable3.Rows[i][5].ToString();
                if (tempVar == "returned")
                    avaialable = "Yes";
                studentHold.Rows.Add(dataTable3.Rows[i][0], dataTable3.Rows[i][2], dataTable3.Rows[i][6], dataTable3.Rows[i][3], avaialable);
            }







        }


        private string ItemAvailable(string id)
        {

            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from loan where (iID = '" + id + "' AND status = 'rented')", connParam);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();
            dAdapter.Fill(dataTable);

            int ifFound = dataTable.Rows.Count;
            if (ifFound > 0)
            {
                return "false";
            }
            else
            {
                return "true";
            }
        }
        private void GetItemDetail(string ID)
        {

            getID(ID);
            sqlCommands result = new sqlCommands();
            string sqlStatement = result.itemDetail(ID);
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(sqlStatement, connParam);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();
            dAdapter.Fill(dataTable);
            string temp;

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                txtitemname.Text = dataTable.Rows[i][1].ToString();
                txtitemcat.Text = dataTable.Rows[i][2].ToString();
                txtitemdesc.Text = dataTable.Rows[i][3].ToString(); ;
                txtitemstatus.Text = dataTable.Rows[i][4].ToString();
                temp = ItemAvailable(dataTable.Rows[i][0].ToString());
                if (temp == "false")
                {
                    txtitemavailable.Text = "No";
                    btnitemrent.Enabled = false;
                }
                else
                {
                    txtitemavailable.Text = "Yes";
                    btnitemrent.Enabled = true;
                }
            }




            sqlStatement = result.itemHistory(ID);
            OleDbDataAdapter dAdapter1 = new OleDbDataAdapter(sqlStatement, connParam);
            OleDbCommandBuilder cBuilder1 = new OleDbCommandBuilder(dAdapter1);
            DataTable dataTable1 = new DataTable();
            DataSet ds1 = new DataSet();
            dAdapter1.Fill(dataTable1);

            for (int i = 0; i < dataTable1.Rows.Count; i++)
            {

                griditemhistory.Rows.Add(dataTable1.Rows[i][0], dataTable1.Rows[i][1], dataTable1.Rows[i][2], dataTable1.Rows[i][6], dataTable1.Rows[i][3], dataTable1.Rows[i][7], dataTable1.Rows[i][5]);
            }



        }


        protected override void OnFormClosing(FormClosingEventArgs e) { Application.Exit(); }

        private void button1_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void addItemToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form frmshow = new addItem();
            frmshow.ShowDialog();


        }

        private void addStudentToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form frmshow = new addStudent();
            frmshow.ShowDialog();
        }

        private void pnlMain_Paint(object sender, PaintEventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (btnEdit.Text == "Edit")
            {
                txtFname.Enabled = true;
                txtLname.Enabled = true;

                txtEmail.Enabled = true;

                txtID.Enabled = true;

                btnCancel.Enabled = true;
                btnEdit.Text = "Update";
            }
            else if (btnEdit.Text == "Update")
            {
                bookConn.Open();
                oleDbCmd.Connection = bookConn;
                sqlCommands result = new sqlCommands();

                string sqlStatement = result.updateStudent(txtFname.Text, currentId);
                oleDbCmd.CommandText = sqlStatement;

                try
                {
                    int temp = oleDbCmd.ExecuteNonQuery();
                    if (temp > 0)
                    {
                        MessageBox.Show("Information Updated.");
                        txtFname.Enabled = false;
                        txtLname.Enabled = false;

                        txtEmail.Enabled = false;

                        txtID.Enabled = false;

                        btnCancel.Enabled = false;
                        btnEdit.Text = "Edit";

                    }
                    else
                    {
                        MessageBox.Show("Error while updating.");
                    }
                }
                catch (Exception ee)
                {
                    MessageBox.Show("Error while updating" + ee);
                }
                bookConn.Close();

            }

        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //     textBox10.Text = listBox1.SelectedItem.ToString();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            gridStudent.DataSource = null;

            gridStudent.Rows.Clear();

            gridStudent.Refresh();

            sqlCommands result = new sqlCommands();

            string sqlStatement = result.searchStudent("remove it later");

            OleDbDataAdapter dAdapter = new OleDbDataAdapter(sqlStatement, connParam);

            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);


            DataTable dataTable = new DataTable();

            DataSet ds = new DataSet();

            dAdapter.Fill(dataTable);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                gridStudent.Rows.Add(dataTable.Rows[i][3], dataTable.Rows[i][1] + " " + dataTable.Rows[i][2]);


            }



        }

        private void dataGridView4_SelectedIndexChanged(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (gridStudent.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                {
                    GetStudentDetails(gridStudent.Rows[e.RowIndex].Cells[0].Value.ToString());

                    loanview.ClearSelection();
                    studentLoanReturn.ClearSelection();
                    studentHold.ClearSelection();

                }
            }
            catch
            {

            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if (lblUser.Text == "")
            {
                MessageBox.Show("Error: No Student Selected");
            }
            else
            {
                Form frmshow = new addLoan(currentId);

                frmshow.ShowDialog();
            }
        }

        private void loanview_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (loanview.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                {
                    selectedloanID = loanview.Rows[e.RowIndex].Cells[0].Value.ToString();
                }
            }
            catch
            {
                //
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            GetStudentDetails(currentId);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            prodReturn(selectedloanID);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            prodReturn(selectedloanID);

        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            txtFname.Enabled = false;
            txtLname.Enabled = false;

            txtEmail.Enabled = false;

            txtID.Enabled = false;

            btnCancel.Enabled = false;
            btnEdit.Text = "Edit";
        }

        private void studentHold_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {


            string availableCheck = "No"; ;
            try
            {
                if (studentHold.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                {
                    availableCheck = studentHold.Rows[e.RowIndex].Cells[4].Value.ToString();
                    selectedloanID1 = studentHold.Rows[e.RowIndex].Cells[0].Value.ToString();
                    bookID = studentHold.Rows[e.RowIndex].Cells[1].Value.ToString();
                    bookName = studentHold.Rows[e.RowIndex].Cells[2].Value.ToString();

                }

                if (availableCheck == "Yes")
                {
                    btnRentNow.Enabled = true;

                }
                else
                {
                    btnRentNow.Enabled = false;

                }

            }
            catch
            {
                //
            }




        }

        private void btnUnhold_Click(object sender, EventArgs e)
        {
            bookConn.Open();
            oleDbCmd.Connection = bookConn;
            oleDbCmd.CommandText = "UPDATE loan SET hold = '' WHERE ID = " + selectedloanID1 + ";";

            try
            {
                int temp = oleDbCmd.ExecuteNonQuery();
                if (temp > 0)
                {

                    MessageBox.Show("Successfully.");

                }
                else
                {
                    MessageBox.Show("Record Fail to Added");
                }
            }
            catch
            {
                MessageBox.Show("Record Fail to Added");
            }
            bookConn.Close();

        }

        private void btnRentNow_Click(object sender, EventArgs e)
        {
            Form rent = new rent(bookID, currentId, bookName, selectedloanID1);
            rent.ShowDialog();
        }

        private void studentLoanReturn_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button9_Click(object sender, EventArgs e)
        {
            gridProd.DataSource = null;

            gridProd.Rows.Clear();

            gridProd.Refresh();

            sqlCommands result = new sqlCommands();

            string sqlStatement = result.searchProd("remove it later");

            OleDbDataAdapter dAdapter = new OleDbDataAdapter(sqlStatement, connParam);

            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);


            DataTable dataTable = new DataTable();

            DataSet ds = new DataSet();

            dAdapter.Fill(dataTable);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                gridProd.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1]);


            }



        }

        private void tabPage5_Click(object sender, EventArgs e)
        {

        }

        private void gridProd_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (gridProd.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                {
                    GetItemDetail(gridProd.Rows[e.RowIndex].Cells[0].Value.ToString());
                    bookID = gridProd.Rows[e.RowIndex].Cells[0].Value.ToString();
                    bookName = gridProd.Rows[e.RowIndex].Cells[1].Value.ToString();
                    loanview.ClearSelection();
                    studentLoanReturn.ClearSelection();
                    studentHold.ClearSelection();
                    griditemhistory.ClearSelection();

                }
            }
            catch
            {

            }
        }

        private void btnitemrent_Click(object sender, EventArgs e)
        {
            Form student = new student(bookID, bookName);
            student.ShowDialog();
        }

        private void button2_Click(object sender, EventArgs e)
        {

        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
                calender1.Enabled = true;
            else
                calender1.Enabled = false;

        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox2.Checked)
                calender2.Enabled = true;
            else
                calender2.Enabled = false;
        }

        private void button1_Click_2(object sender, EventArgs e)
        {
            gridLoan.Rows.Clear();
            gridLoan.Refresh();

            DateTime start = calender1.SelectionRange.Start;
            string dateFormat = "dd/MM/yyyy";
            DateTime end = calender2.SelectionRange.Start;

            string searchstartdate;
            string searchenddate;

            if (calender1.Enabled)
            {
                searchstartdate = start.ToString(dateFormat);
            }
            else
            {
                searchstartdate = "01/01/2001";
            }

            if (calender2.Enabled)
            {
                searchenddate = end.ToString(dateFormat);
            }
            else
            {
                searchenddate = "01/01/2099";
            }



            string sqlstatement;

            string range = cbrange.Text.ToString();
            string rent = cbrent.Text.ToString();

            sqlCommands result = new sqlCommands();

            sqlstatement = result.sqlforCalender(searchstartdate, searchenddate, "FF", rent);



            OleDbDataAdapter dAdapter77 = new OleDbDataAdapter(sqlstatement, connParam);

            OleDbCommandBuilder cBuilder77 = new OleDbCommandBuilder(dAdapter77);


            DataTable dataTable77 = new DataTable();

            DataSet ds77 = new DataSet();

            dAdapter77.Fill(dataTable77);



            string queryDate;


            DateTime startDate;
            DateTime endDate;
            startDate = new DateTime();
            endDate = new DateTime();
            startDate = DateTime.ParseExact(searchstartdate, "dd/MM/yyyy", null);
            endDate = DateTime.ParseExact(searchenddate, "dd/MM/yyyy", null);







            for (int i = 0; i < dataTable77.Rows.Count; i++)
            {
                DateTime time = DateTime.Now;
                DateTime returnDate = DateTime.Now;
                if (range == "End Date")
                {
                    queryDate = dataTable77.Rows[i][4].ToString();
                }
                else
                    queryDate = dataTable77.Rows[i][3].ToString();
                DateTime query;
                query = new DateTime();

                query = DateTime.ParseExact(queryDate, "dd/MM/yyyy", null);

                if ((query > startDate) && (query < endDate))
                {
                    if (dataTable77.Rows.Count > 0)
                        gridLoan.Rows.Add(dataTable77.Rows[i][0], dataTable77.Rows[i][1], dataTable77.Rows[i][6], dataTable77.Rows[i][3], dataTable77.Rows[i][4]);
                    else
                        gridLoan.Rows.Clear();
                }
            }




        }

        private void button6_Click(object sender, EventArgs e)
        {
            prodReturn(selectedloanID);
        }

        private void gridLoan_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            selectedloanID = gridLoan.Rows[e.RowIndex].Cells[0].Value.ToString();
        }

        private void closeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("SSS");
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
           
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void optionsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form option = new option();

            option.ShowDialog();
        }

        public void notification(int totalDays)
        {
            int totalNotifications = 0;
            DateTime today = DateTime.Now;
           
            
            string searchEnd = today.AddDays(totalDays).ToString();

            DateTime endDate = Convert.ToDateTime(searchEnd);
            string sqlstatement;

            string searchstartdate = today.ToString();
            string searchenddate = endDate.ToString();



            sqlCommands result = new sqlCommands();
            sqlstatement = result.sqlforCalender(searchstartdate, searchenddate, "FF", "NO");

            OleDbDataAdapter dAdapter45 = new OleDbDataAdapter(sqlstatement, connParam);
            OleDbCommandBuilder cBuilder45 = new OleDbCommandBuilder(dAdapter45);

            DataTable dataTable45 = new DataTable();
            DataSet ds45 = new DataSet();
            dAdapter45.Fill(dataTable45);


            string queryDate;

           

            for (int i = 0; i < dataTable45.Rows.Count; i++)
            {
                DateTime time = DateTime.Now;
                DateTime returnDate = DateTime.Now;

                queryDate = dataTable45.Rows[i][4].ToString();
                DateTime query;
                query = new DateTime();

                query = DateTime.ParseExact(queryDate, "dd/MM/yyyy", null);

                if ((query > today) && (query < endDate))
                {
                    if (dataTable45.Rows.Count > 0)
                    {
                        gridNotification.Rows.Add(dataTable45.Rows[i][0], dataTable45.Rows[i][1], dataTable45.Rows[i][6], dataTable45.Rows[i][3], dataTable45.Rows[i][4]);
                        totalNotifications++;
                    }
                    else
                        gridNotification.Rows.Clear();
                }
               tabPage6.Text = totalNotifications +" Notification(s)";
            }
        }
        
        private void button3_Click_2(object sender, EventArgs e)
        {

            int totalDays;
            totalDays = Convert.ToInt16(cb1.SelectedItem);
            try
            {
                string week = comboBox3.SelectedItem.ToString();

                if (week == "Week")
                {
                    totalDays = totalDays * 7;
                }
            }
            catch
            {

            }
           
            
            gridNotification.Rows.Clear();
            gridNotification.Refresh();

            notification(totalDays);


           

        }

        private void button8_Click_1(object sender, EventArgs e)
        {
            
            
          
            
        }

        private void connectDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
        {
            OpenFileDialog o = new OpenFileDialog();
            o.Filter = "Database File (*.mdb)|*.mdb";


            o.ShowDialog();

            if (o.FileName.ToString() != "")
            {

            DialogResult dialogResult = MessageBox.Show("Connect to database: " + o.FileName.ToString(), "", MessageBoxButtons.YesNo);

                if (dialogResult == DialogResult.Yes)
                {
                    string setLocation = o.FileName.ToString();
                    Properties.Settings.Default.database = setLocation;
                }
                else if (dialogResult == DialogResult.No)
                {
                    //
                }
            }
            Properties.Settings.Default.Save();
            connParam = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Properties.Settings.Default.database + ";Persist Security Info=False";
        }

        private void button10_Click(object sender, EventArgs e)
        {
           
        }

        private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void button8_Click_2(object sender, EventArgs e)
        {
            
        }

        private void backupDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
        {

            SaveFileDialog o = new SaveFileDialog();
            o.Filter = "All files (*.*)|*.*";


            o.ShowDialog();



            EncryptFile(Properties.Settings.Default.database.ToString(), o.FileName.ToString());

            }
        private void EncryptFile(string inputFile, string outputFile)
        {
            try
            {
                string password = @"defaultk"; // Your Key Here
                UnicodeEncoding UE = new UnicodeEncoding();
                byte[] key = UE.GetBytes(password);
                
                outputFile += ".dbk";
                FileStream fsCrypt = new FileStream(outputFile, FileMode.Create);
                RijndaelManaged RMCrypto = new RijndaelManaged();
                CryptoStream cs = new CryptoStream(fsCrypt, RMCrypto.CreateEncryptor(key, key), CryptoStreamMode.Write);
               
                FileStream fsIn = new FileStream(inputFile, FileMode.Open);
                int data;
                while ((data = fsIn.ReadByte()) != -1)
                    cs.WriteByte((byte)data);


                fsIn.Close();
                cs.Close();
                fsCrypt.Close();

               
                MessageBox.Show("\nBackup successfully completed.....");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Backup failed... \n\n" + ex.ToString());
            }



        }

        private void reportBugToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form next = new reportBug();
            next.ShowDialog();
        }
        }


        


    }
    

